﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ProgNetComponents.Data.SqlCe;
using System.Data.SqlServerCe;
using ProgNetComponentsDx.Options;
using ProgNetComponents.Classes;
using System.Data;
using BuildHelper.Init.Defaults;

namespace BuildHelper
{
    public static class AppUpdate
    {
        static SqlCeDataManager helper = new SqlCeDataManager();
        
        static List<string> Queries = new List<string>(){
            "ALTER TABLE STP_SRODOWISKA ADD 	STP_SR_FAV bit DEFAULT '0' NOT NULL;",
            "ALTER TABLE STP_SERWERY ADD 	STP_SRW_HOST nvarchar(50) DEFAULT 'localhost' NOT NULL;",
            "ALTER TABLE STP_SERWERY ADD 	STP_SRW_AKTYWNY bit DEFAULT '1' NOT NULL;",
            "ALTER TABLE STP_VISUAL_STUDIO ADD 	STP_VS_EXPRESS bit DEFAULT '0' NOT NULL;",
           Properties.Resources.UpdateSql
        };

        public static void Update(int fromAction)
        {
            List<Action> actions = new List<Action>();
            bool executed = false;
            #region add actions
            actions.Add(CreateApp);
            actions.Add(AddServerLastDB);
            actions.Add(AddServerLastBR);
            actions.Add(AddBranczActive);
            actions.Add(AddOptsTabs);
            actions.Add(AddUserToEnv);
            actions.Add(UpdateIp);
            actions.Add(UpdateSTP_BR_APP_APP_FK);
            actions.Add(AddQueryUpdate);
            actions.Add(AddSqlTypeBrowser);
            actions.Add(() => { STP_APPS.AddApp(null, "IDX"); });
            #endregion

            if (fromAction == 0)
            {
                CreateApp();
                helper.ExecuteNonQuery("UPDATE STP_OPCJE SET STP_OP_WARTOSC = '" + actions.Count + "' WHERE STP_OP_NAZWA = 'DB_UPDATE'");
                helper.ExecuteNonQuery("UPDATE STP_OPCJE SET STP_OP_WARTOSC = '" + Queries.Count + "' WHERE STP_OP_NAZWA = 'NEXT_UPDATE_QUERY_NO'");
            }
            else
            {
                #region execute queries
                ExecuteQueries();
                #endregion

                #region execute actions
                for (int i = fromAction; i < actions.Count; i++)
                {
                    executed = true;
                    try
                    {
                        actions[i].Invoke();
                    }
                    catch (Exception ex)
                    {
                        ex.Log("Błąd wykonania akcji " + i);
                    }
                }
                if (executed)
                {
                    int updated = 0;

                    try
                    {
                        updated = helper.ExecuteNonQuery("UPDATE STP_OPCJE SET STP_OP_WARTOSC = '" + actions.Count + "' WHERE STP_OP_NAZWA = 'DB_UPDATE'");
                    }
                    catch { }
                    if (updated == 0)
                    {
                        try
                        {
                            helper.ExecuteNonQuery("INSERT INTO STP_OPCJE (STP_OP_WARTOSC, STP_OP_NAZWA) VALUES ('" + actions.Count + "', 'DB_UPDATE')");
                        }
                        catch { }
                    }
                }
                #endregion
            }
        }

        private static void AddUserToEnv()
        {
            ExecuteQieries("ALTER TABLE STP_SRODOWISKA ADD STP_SR_USER nvarchar(50) NULL;", "Add STP_SR_USER");
        }
        private static void CreateApp()
        {
            SqlCeEngine eng = new SqlCeEngine(SqlCeDataManager.GlobalConnectionString);
            try
            {
                eng.CreateDatabase();
            }
            catch { }
            ExecuteQieries(Properties.Resources.SqlScripts, "CreateApp");
            ExecuteQieries(Properties.Resources.InsertVS, "CreateApp - InsertVS");
            ExecuteQieries(Properties.Resources.InsertKonfigi, "CreateApp - InsertKonfigi");
            //ExecuteQieries(Properties.Resources.InsertApps, "CreateApp - InsertApps"); 
            BuildHelper.Init.Defaults.STP_APPS.CreateDefault();

            ExecuteQieries("INSERT INTO STP_OPCJE(STP_OP_WARTOSC, STP_OP_NAZWA) VALUES ('1', 'DB_UPDATE')", "INSERT OPCJE");
            ExecuteQieries("INSERT INTO STP_OPCJE(STP_OP_WARTOSC, STP_OP_NAZWA) VALUES ('0', 'NEXT_UPDATE_QUERY_NO')", "Insert NEXT_UPDATE_QUERY_NO");
            AddSqlTypeBrowser();
        }

        private static void AddServerLastDB()
        {
            ExecuteQieries("ALTER TABLE STP_SERWERY ADD STP_SRW_LAST_DB nvarchar(50) NULL;", "Add STP_SRW_LAST_DB");
        }
        private static void AddServerLastBR()
        {
            ExecuteQieries("ALTER TABLE STP_SERWERY ADD STP_SRW_LAST_BR nvarchar(50) NULL;", "Add STP_SRW_LAST_BR");
        }

        private static void AddBranczActive()
        {
            string q = @"ALTER TABLE STP_BRANCZE ADD 	STP_BR_AKTYWNY bit DEFAULT '1' NULL;
GO;
ALTER TABLE STP_BRANCZE  ALTER COLUMN STP_BR_AKTYWNY  bit NOT NULL;
GO; ";
            ExecuteQieries(q, "AddBranczActive");
        }
        private static void AddOptsTabs()
        {
            string query = @"
CREATE TABLE STP_OPCJE_TABS(
	STP_OPT_TAB nvarchar(4) NOT NULL,
	STP_OPT_LOKALIZACJA nvarchar(150) NOT NULL,
	STP_OPT_ID int IDENTITY (1, 1) NOT NULL
);
GO;

ALTER TABLE STP_OPCJE_TABS ADD CONSTRAINT STP_OPCJE_TABS_PK PRIMARY KEY (STP_OPT_ID);
GO;
";
            ExecuteQieries(query, "AddOptsTabs");
        }

        private static void ExecuteQieries(string queries, string name)
        {
            string[] str = queries.XSplit(@"\s+GO;\s+", System.Text.RegularExpressions.RegexOptions.IgnoreCase | System.Text.RegularExpressions.RegexOptions.Multiline);
            string query;

            for (int i=0; i < str.Length; i++)
            {
                try
                {
                    query = str[i].Trim().XReplace("GO;$", "", System.Text.RegularExpressions.RegexOptions.IgnoreCase | System.Text.RegularExpressions.RegexOptions.Singleline);

                    if (query.IsNotNull())
                    {
                        helper.ExecuteNonQuery(query);
                        if (helper.LastException != null)
                            LogException(name, query, i, helper.LastException);
                    }
                }
                catch (Exception ex)
                {
                    LogException(name, str[i], i, ex);
                }
            }
        }
        private static void UpdateIp()
        {
            ExecuteQieries("UPDATE STP_OPCJE SET STP_OP_WARTOSC = '172.29.3.5' WHERE STP_OP_NAZWA = " + "BuildHelper.Init.InitFormOptionNames.Kontakty.Server.HostIp".EscapeSql(), "UpdateIp");
        }
        private static void LogException(string name, string query, int no, Exception ex)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("-------------------------------------------");
            sb.AppendLine("Błąd w skrypcie: " + name + ", polecenie numer: " + no);
            sb.AppendLine("Skrypt: " + query);
            ex.LogError(sb.ToString());
        }
        private static void UpdateSTP_BR_APP_APP_FK()
        {
            ExecuteQieries(@"
ALTER TABLE STP_BRANCZE_APPS DROP CONSTRAINT STP_BR_APP_APP_FK;
GO;
ALTER TABLE STP_BRANCZE_APPS ADD CONSTRAINT STP_BR_APP_APP_FK FOREIGN KEY (STP_BR_APP_APP) REFERENCES STP_APPS(STP_APP_KOD) ON DELETE NO ACTION ON UPDATE CASCADE;
GO;", "Update STP_BR_APP_APP_FK");
        }
        private static void AddQueryUpdate()
        {
            ExecuteQieries("INSERT INTO STP_OPCJE(STP_OP_WARTOSC, STP_OP_NAZWA) VALUES ('0', 'NEXT_UPDATE_QUERY_NO')", "Insert NEXT_UPDATE_QUERY_NO");
        }
        
        static void ExecuteQueries()
        {
            int nextNo = helper.ExecuteScalar("SELECT STP_OP_WARTOSC from STP_OPCJE where STP_OP_NAZWA = 'NEXT_UPDATE_QUERY_NO'").AsInt();
            for (int i = nextNo; i < Queries.Count; i++)
                ExecuteQieries(Queries[i], "Query " + i);
           
            int c = helper.ExecuteNonQuery("UPDATE STP_OPCJE SET STP_OP_WARTOSC = '" + Queries.Count + "' WHERE STP_OP_NAZWA = 'NEXT_UPDATE_QUERY_NO'");
            if (c == 0)
            {
                ExecuteQieries("INSERT INTO STP_OPCJE(STP_OP_WARTOSC, STP_OP_NAZWA) VALUES ('" + Queries.Count + "','NEXT_UPDATE_QUERY_NO')", "Insert NEXT_UPDATE_QUERY_NO");
            }
        }

        static void AddSqlTypeBrowser()
        {
            ExecuteQieries("INSERT INTO STP_TYPY_SQL(STP_TPS_NAZWA) VALUES ('VARCHAR2')", "AddSqlTypeBrowser");
            ExecuteQieries("INSERT INTO STP_TYPY_SQL(STP_TPS_NAZWA) VALUES ('NUMBER')", "AddSqlTypeBrowser");
        }
    }

}
